/**
 * @FileName: VnetService.java
 * @Author
 * @Description:
 * @Date 2016年9月1日 上午10:52:37
 * @CopyRight ZTE Corporation
 */
package cn.hpclub.server.service;

import java.util.Map.Entry;
import java.util.concurrent.ConcurrentHashMap;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.hpclub.server.constant.Constant;
import cn.hpclub.server.util.InterfaceUtil;

public class VnetService{
    private static final Logger  logger         = LoggerFactory.getLogger(BroadbandService.class);
    public static VnetService    service        = new VnetService();

    public final static String   SELECT         = "SELECT vnet_records.*,org.NumCode as channel_code,org.DisplayName as channel_name,org_class.class_name as channel_type,date_format(vnet_records.time,'%Y/%m/%d %H:%i:%s') as opTime,(case vnet_records.v_type when 0 then '校园V网' when 1 then '合家欢' when 2 then '乡音网' end) as vType,(case vnet_records.op_type when 0 then '添加成员' when 1 then '删除成员' end) as opType,IF(orgType.orgIDTmp||orgType2.orgIDTmp2, '是', '否') AS is_accounts_manager ";

    public static final String[] HEADES_EXPORT  = { "V网类型", "V网名称", "V网编码", "业务类型", "V网成员", "成员短号", "资费名称", "费率",
            "店员姓名", "店员手机", "地市", "区县", "渠道编码", "渠道名称", "渠道类型", "客户经理", "办理时间", "备注" };
    public static final String[] COLUMNS_EXPORT = { "vType", "v_name", "v_code", "opType", "v_member", "v_short",
            "v_pkg_name", "v_pkg_rate", "user_name", "user_mobile", "city", "quxian", "channel_code", "channel_name",
            "channel_type", "is_accounts_manager", "opTime", "remark" };

    public static String getSelectSql(){
        String citySql = InterfaceUtil.getCitySql("member_org.org_id");
        String quxianSql = InterfaceUtil.getAreaSql("member_org.org_id");

        logger.debug("citySql:" + citySql);
        logger.debug("quxianSql:" + quxianSql);

        String sql = "SELECT vnet_records.*,"
                + "org.NumCode as channel_code,org.DisplayName as channel_name,org_class.class_name as channel_type,"
                + citySql + " as city," + quxianSql + " as quxian,"
                + "date_format(vnet_records.time,'%Y-%m-%d %H:%i:%s') as opTime,(case vnet_records.v_type when 0 then '校园V网' when 1 then '合家欢' when 2 then '乡音网' end) as vType,(case vnet_records.op_type when 0 then '添加成员' when 1 then '删除成员' end) as opType,IF(orgType.orgIDTmp||orgType2.orgIDTmp2, '是', '否') AS is_accounts_manager ";
        return sql;
    }

    public String getSqlExceptSelect(ConcurrentHashMap<String, String> searchInfo){
        int topId = InterfaceUtil.getIdTop();
        String sqlExceptSelect = "FROM vnet_records,member_org,organization_class org_class,organization org"
                + " LEFT JOIN (SELECT id AS orgIDTmp FROM `organization` where (parentid=" + topId
                + " OR parentid IN (select id FROM organization WHERE  parentid=" + topId + "))) AS orgType "
                + " ON org.id = orgType.orgIDTmp "
                + " LEFT JOIN (SELECT org.id AS orgIDTmp2, orgcls.WebsiteId AS WebsiteIdType FROM organization org, organization_class orgcls WHERE org.WebsiteId = orgcls.id AND org.enabled=1 AND orgcls.enabled=1 AND ( orgcls.WebsiteId LIKE '6%' OR orgcls.WebsiteId = '1000' OR orgcls.WebsiteId = '4800')) AS orgType2 ON org.id = orgType2.orgIDTmp2"
                + " where vnet_records.user_id =member_org.id and member_org.org_id=org.id and org.WebsiteId = org_class.id";

        for(Entry<String, String> item : searchInfo.entrySet()){
            sqlExceptSelect += buildQuery(item);
        }

        sqlExceptSelect += " order by vnet_records.time desc";

        logger.debug("sqlExceptSelect = " + sqlExceptSelect);
        return sqlExceptSelect;
    }

    private String buildQuery(Entry<String, String> item){
        String sql = "";
        if(item.getKey().equals("username")){
            // 办理人姓名
            sql += " and vnet_records.user_name like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("usermobile")){
            // 办理人手机
            sql += " and vnet_records.user_mobile  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("member")){
            // 成员手机
            sql += " and vnet_records.v_member  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("vgroupname")){
            // V网名称
            sql += " and vnet_records.v_name  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("vgroupcode")){
            // V网编码
            sql += " and vnet_records.v_code  like '%" + item.getValue() + "%' ";
        } else if(item.getKey().equals("startTime")){
            sql += " and vnet_records.time >= '" + item.getValue() + " 00:00:00" + "' ";
        } else if(item.getKey().equals("endTime")){
            sql += " and vnet_records.time <= '" + item.getValue() + " 23:59:59" + "' ";
        } else if(item.getKey().startsWith("isMgr")){
            if("1".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is not null) or (orgType2.orgIDTmp2 is not null)) ";
            } else if("0".equals(item.getValue())){
                sql += " and ((orgType.orgIDTmp is  null) and (orgType2.orgIDTmp2 is null)) ";
            }

        } else if(item.getKey().equals("org_id")){
            sql += " and member_org.org_id in("
                    + InterfaceUtil.getChlidOrgIdSql(Integer.parseInt(item.getValue()), Constant.MAX_ORG_LEVEL, true)
                    + ") ";
        } else if (item.getKey().equals("vgrouptype") && !"-1".equals(item.getValue())) {
    		sql += " and vnet_records.v_type = " + item.getValue();
        }
        return sql;
    }
}
